package cn.lfzj.dao;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import cn.lfzj.model.Sequence;

public interface SequenceDao {
	
	/**
	 * 查询所有班次
	 */
	@Select("SELECT * FROM bus_sequence order by line_id,start_time")
	@Results({
			@Result(property = "busLine", column = "line_id", one = @One(select = "cn.lfzj.dao.BusLineDao.selById")) })
	public List<Sequence> findAll();

	/**
	 * 新增班次
	 */
	@Insert("INSERT INTO bus_sequence " + "VALUES(#{sequenceId},#{busLine.lineId},#{startTime},#{endTime},#{runState})")
	public int addSequence(Sequence Sequence);

	/**
	 * 删除班次
	 */
	@Delete("DELETE FROM bus_sequence WHERE sequence_id=#{sequenceId}")
	public int delSequence(String sequenceId);

	/**
	 * 修改班次
	 */
	@Update("UPDATE bus_sequence "
			+ "SET line_id=#{busLine.lineId},start_time=#{startTime},end_time=#{endTime},run_state=#{runState} "
			+ "WHERE sequence_id=#{sequenceId}")
	public int updSequence(Sequence Sequence);

	/**
	 * 根据ID查班次
	 */
	@Select("SELECT * FROM bus_sequence WHERE sequence_id=#{sequenceId}")
	@Results({
			@Result(property = "busLine", column = "line_id", one = @One(select = "cn.lfzj.dao.BusLineDao.selById")) })
	public Sequence selById(String sequenceId);

	/**
	 * 根据线路ID查班次
	 */
	@Select("SELECT * FROM bus_sequence WHERE line_id=#{lineId}")
	public List<Sequence> selByLineId(String lineId);

	/**
	 * 班次是否存在
	 */
	@Select("SELECT * FROM bus_sequence "
			+ "WHERE sequence_id=#{sequenceId} and start_time=#{startTime} and end_time=#{endTime} and run_state=#{runState}")
	public Sequence find(Sequence Sequence);

	/**
	 * 查询未发车班次
	 */
	@Select("SELECT " + "bl.start_station `busLine.startStation`," + "bl.end_station `busLine.endStation`,"
			+ "bl.ticket_price `busLine.ticketPrice`," + "bs.* " + "FROM bus_sequence bs " + "LEFT JOIN bus_line bl "
			+ "ON bs.line_id=bl.line_id "
			+ "WHERE time_to_sec(timediff(concat(DATE_FORMAT(NOW(), '%Y'),'-',#{date},' ',bs.start_time),now()))>300 "
			+ "AND bl.line_id=#{lineId} " + "AND bs.run_state='运行' " + "ORDER BY bs.start_time")
	public List<Sequence> findNotStart(@Param("date") String date, @Param("lineId") String lineId);
}
